package com.medical.standard.dao;

import com.medicalstandard.entity.Inspection;
import com.medicalstandard.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class InspectionDAO {

    public boolean addInspection(Inspection inspection) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO inspection (standard_id, project_num, project_name, project_requirement) VALUES (?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, inspection.getStandardId());
            pstmt.setString(2, inspection.getProjectNum());
            pstmt.setString(3, inspection.getProjectName());
            pstmt.setString(4, inspection.getProjectRequirement());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    public boolean updateInspection(Inspection inspection) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE inspection SET standard_id=?, project_num=?, project_name=?, project_requirement=? WHERE inspection_id=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, inspection.getStandardId());
            pstmt.setString(2, inspection.getProjectNum());
            pstmt.setString(3, inspection.getProjectName());
            pstmt.setString(4, inspection.getProjectRequirement());
            pstmt.setInt(5, inspection.getInspectionId());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    public boolean deleteInspection(int inspectionId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM inspection WHERE inspection_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, inspectionId);

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    public List<Inspection> getInspectionsByStandardId(int standardId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Inspection> inspections = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM inspection WHERE standard_id=? ORDER BY project_num";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, standardId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Inspection inspection = new Inspection();
                inspection.setInspectionId(rs.getInt("inspection_id"));
                inspection.setStandardId(rs.getInt("standard_id"));
                inspection.setProjectNum(rs.getString("project_num"));
                inspection.setProjectName(rs.getString("project_name"));
                inspection.setProjectRequirement(rs.getString("project_requirement"));
                inspections.add(inspection);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return inspections;
    }

    public Inspection getInspectionById(int inspectionId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Inspection inspection = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM inspection WHERE inspection_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, inspectionId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                inspection = new Inspection();
                inspection.setInspectionId(rs.getInt("inspection_id"));
                inspection.setStandardId(rs.getInt("standard_id"));
                inspection.setProjectNum(rs.getString("project_num"));
                inspection.setProjectName(rs.getString("project_name"));
                inspection.setProjectRequirement(rs.getString("project_requirement"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return inspection;
    }
}